/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.mycompany.inserthyperlinks;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 *
 * @author asevciuc
 */
public class OfficeWorkBook {
   

    public void testUpdateFile(File file,String fileName, String sheetName, Integer columnNumber,Integer columnNumberDate,Integer columnNumberCode, Integer fromRowNumber, List<File> imageFilesList) throws FileNotFoundException, IOException {
        Workbook wb = OfficeWorkBook.getWorkBook(file); // or new

        // HSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();

        // cell style for hyperlinks
        // by default hypelrinks are blue and underlined
        CellStyle hlink_style = wb.createCellStyle();
        Font hlink_font = wb.createFont();
        hlink_font.setUnderline(Font.U_SINGLE);
        hlink_font.setColor(IndexedColors.BLUE.getIndex());
        hlink_style.setFont(hlink_font);

        Cell cell;
       
        Sheet sheet = wb.getSheet(sheetName);
        
        Hyperlink link;
        fromRowNumber-=1;
        columnNumber-=1;
        columnNumberDate-=1;
        columnNumberCode-=1;
        for (int i = 0; i < imageFilesList.size(); i++) {
            Row row = sheet.createRow(i+fromRowNumber);
            
            //Insert date
            String date= imageFilesList.get(i).getName();
            date=date.substring(0, date.indexOf(" "));
            cell = row.createCell(columnNumberDate.shortValue());
            cell.setCellValue(date);
            
            // link to a file in the current directory
            String linkToFile=deleteFirstPartOfFilePath(file.getParent(), imageFilesList.get(i).getAbsolutePath());
            cell = row.createCell(columnNumber.shortValue());
            cell.setCellValue(linkToFile);
            link = createHelper.createHyperlink(Hyperlink.LINK_FILE);
            link.setAddress(linkToFile);
            cell.setHyperlink(link);
            cell.setCellStyle(hlink_style);
            
            //Insert code
            String parent=imageFilesList.get(i).getParent();
            parent=parent.substring(parent.lastIndexOf("\\")+1);
            cell = row.createCell(columnNumberCode.shortValue());
            cell.setCellValue("MD"+parent);
        }

        String parent=file.getParent();
        parent+="\\Updated - "+file.getName();
        FileOutputStream out = new FileOutputStream(parent);
        wb.write(out);
        out.close();
        
    }
    private String deleteFirstPartOfFilePath(String parent,String fullPath){
        parent= parent.substring(parent.lastIndexOf("\\")+1);
        String path=fullPath.substring(fullPath.indexOf(parent)+parent.length()+1);
        return path.replace("\\", "/");
    }

    public static Workbook getWorkBook(File file) throws FileNotFoundException, IOException {
        Workbook workbook=null;
        if(file.getName().contains("xlsx")){
            workbook =new XSSFWorkbook(new FileInputStream(file));
        }else{
           workbook= new HSSFWorkbook(new FileInputStream(file));
        }
        return workbook;
    }
}
